CREATE PROC [dbo].[asi_GenerateSalutations] (
    @contactKey uniqueidentifier
)
AS
BEGIN

-- This stored procedure inserts all the required ContactSalutation records for a given
-- contact.  This is useful for triggers that insert contacts and need to insert the
-- required contact salutations as well.

SET NOCOUNT ON

DECLARE @salutationKey uniqueidentifier
DECLARE @tempKey uniqueidentifier
DECLARE @sql nvarchar(2000)
DECLARE @individualFormula nvarchar(2000)
DECLARE @instituteFormula nvarchar(2000)
DECLARE @calcFormula nvarchar(2000)
DECLARE @tempValue nvarchar(1000)
DECLARE @isInstitute bit
DECLARE @isOverridden bit

SELECT @isInstitute = IsInstitute FROM ContactMain where ContactKey = @contactKey
IF @@ROWCOUNT = 0
    RETURN

SELECT @individualFormula = IndividualFormula, @instituteFormula = InstituteFormula
    FROM SalutationRef
    WHERE SalutationRef.SalutationKey=@salutationKey AND AutoCreateFlag = 1
IF @@ROWCOUNT = 0
    RETURN

DECLARE theSalutationCursor CURSOR FAST_FORWARD FOR
    SELECT SalutationKey, IndividualFormula, InstituteFormula
        FROM SalutationRef
        WHERE AutoCreateFlag = 1

OPEN theSalutationCursor
FETCH NEXT FROM theSalutationCursor INTO @salutationKey, @individualFormula, @instituteFormula

WHILE @@FETCH_STATUS = 0 -- spin through auto-create salutations
BEGIN

    SET @tempKey = newid()

    SELECT @sql =  
        CASE
        WHEN @isInstitute = 1 THEN
            ' asi_ProcessFormulaOutput '
                + '''' + convert (nvarchar(100), @tempKey) + ''', '
                + '''' + @instituteFormula + ''', '
                + '''Institute'', ''ContactKey'', '
                + '''' + convert (nvarchar(50), @contactKey)
                + ''''

        WHEN @isInstitute = 0 THEN
            ' asi_ProcessFormulaOutput '
                + '''' + convert (nvarchar(100), @tempKey) + ''', '
                + '''' + @individualFormula + ''', '
                + '''Individual'', ''ContactKey'', '
                + '''' + convert (nvarchar(50), @contactKey)
                + ''''
        END
    FROM ContactMain WHERE ContactKey = @contactKey

    EXEC (@sql)

    SELECT @tempValue = tempValue
        FROM tempFormula
        WHERE tempKey = @tempKey

    IF @tempValue IS NOT NULL AND datalength (@tempValue) > 0
    BEGIN
        SELECT @isOverridden = IsOverridden FROM ContactSalutation
            WHERE ContactKey = @contactKey AND SalutationKey = @salutationKey
        IF @isOverridden IS NULL
        BEGIN
            INSERT ContactSalutation
                (ContactSalutationKey, IsOverridden, SalutationText, ContactKey, SalutationKey, IsDeletable)
            VALUES (@tempKey, 0, @tempValue, @contactKey, @salutationKey, 1)
        END
        ELSE
        BEGIN
            IF @isOverridden = 0
            BEGIN
                UPDATE ContactSalutation set IsDeletable = 1, SalutationText = @tempValue
                WHERE ContactKey = @contactKey AND SalutationKey = @salutationKey
            END
        END
    END

    DELETE FROM tempFormula WHERE tempKey = @tempKey

FETCH NEXT FROM theSalutationCursor INTO @salutationKey, @individualFormula, @instituteFormula
END  -- spin through Salutation entries

CLOSE theSalutationCursor
DEALLOCATE theSalutationCursor

SET NOCOUNT OFF

END

GO